<?php 

require_once('database-connector.php');

function startsWith($haystack, $needle)
{
    $length = strlen($needle);
    return (substr($haystack, 0, $length) === $needle);
}
function endsWith($Haystack, $Needle) {
    return strrpos($Haystack, $Needle) === strlen($Haystack)-strlen($Needle);
}

class BaseProvider {
	
	/**
	 $table_def [array] MUST conform with this rule.
	 
	Note: 
	 	root table will ommit {table_name}, which will specificed by the first argument of parent constructor instead.
	 
	Format: 
		 {table_name} = Array -- Meta table data.
		 (
		 	[id] => {db field name}
		 	[ref{i}] => {db field name reference for its parent, i}
		 	[doc] => {date of creation field name}
		 	[doe] => {date of edit field name}
		 	[sub] => {array of sub table definition, using Meta table format}
		 	[ondelete] => Array (
		 		'delete' => Array (
		 			{table to be delete} => {reference field name}
			 	)
			)
		 )
	
	Explain:
		def: [id] - required, this key will points out the most important key in your table. intuitively, this is true primary key field name. 

		def: [ref{i}] - required as nested down where {i} is an index, started from 0. If your table was nested down to second level upto {i} = 0 is required. In other words, number of ref{i} required = the depth of your table. Value of ref{i} is a database field name of the key that refrence back to your parent, ref0 is always the top most table, and so on.

		def: [doc] - optional, if specific, this provider will keep track of this particular field, and treat it as 'date of creation', the value will be updated only when WRITE (insert) is called. NOTE that this value can be overthrown by mannual specify the value in $_data parameter of WRITE function.

		def: [doe] - optional, if specific, this provider will keep track of this particular field, and treat it as 'last update', the value will be updated every time WRITE function is invoked (in every success case). NOTE that this value can be overthrown by mannual specify the value in $_data parameter of WRITE function.

		def: [sub] - optional, if specify, this provider will recognized another table that was nested underneath this main_table. Value to be put in must be an array with key of 'table_name' and value of 'table_definition'. Table to be put in 'sub' must be an immediate table. (Child of your table). 

		def: [ondelete] - optional, EXPERIMENTAL, cascade delete feature: This feature allow only direct database manipulation. IT DOES NOT MAKE A CROSS PROVIDER CALLS. If ondelete is set. Upon delete call from this provider, system will invoke each action list. Each action list provided different action as follows:
		 - 'delete', delete followed by simple array with 'table name' as key and 'field name' as value. Each of these table will be deleted on where 'field name' IN (deleted ids).

		Please see *Example* for more information.
		 
	Example:
		Mock up database:
			Consider 4 followings database tables which are nested down, while main_table is root.

				CREATE TABLE 'main_table' (
					'pk' BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
					'val' TEXT NULL,
					'created' DATETIME NOT NULL 
				);

				CREATE TABLE 'sub_table_1' (
					'mk' BIGINT(20) UNSIGNED NOT NULL,
					'sk' BIGINT(20) UNSIGNED NOT NULL,
					'val' TEXT NULL,
					'edited' DATETIME NOT NULL,
					PRIMARY KEY('mk', 'sk'),
					FOREIGN KEY 'mk' REFRENCES main_table('pk')
				);

				CREATE TABLE 'sub_sub_table' (
					'mk' BIGINT(20) UNSIGNED NOT NULL,
					'sk' BIGINT(20) UNSIGNED NOT NULL,
					'ssk' BIGINT(20) UNSIGNED NOT NULL,
					'val' TEXT NULL,
					'edited' DATETIME NOT NULL,
					PRIMARY KEY('mk', 'sk', 'ssk'),
					FOREIGN KEY 'mk' REFRENCES main_table('pk'),
					FOREIGN KEY 'sk' REFRENCES sub_table('sk')
				);

				CREATE TABLE 'sub_table_2' (
					'mk' BIGINT(20) UNSIGNED NOT NULL,
					'sk2' BIGINT(20) UNSIGNED NOT NULL,
					'val' TEXT NULL,
					'created' DATETIME NOT NULL,
					PRIMARY KEY('mk', 'sk2'),
					FOREIGN KEY 'mk' REFRENCES main_table('pk')
					FOREIGN KEY 'sk2' REFRENCES referenced_table('rt_mk')
				);

				CREATE TABLE 'referenced_table' (
					'rt_mk' BIGINT(20) UNSIGNED NOT NULL PRIMARY KEY,
					'created' DATETIME NOT NULL,
					'updated' DATETIME NOT NULL,
					'val' TEXT NULL
				);
		
	Definition: main_table
	 <pre>
		 Array
		 (
		 	[id] => 'pk',
		 	[doc] => 'created',
		 	[sub] => Array
		 	(
		 		'sub_table_1' => Array
		 		(
		 			[ref0] => 'mk',
		 			[id] => 'sk',
		 			[doe] => 'edited',
		 			[sub] => Array
		 			(
		 				'sub_sub_table' => Array
		 				(
		 					[ref0] => 'mk',
		 					[ref1] => 'sk',
		 					[id] => 'ssk',
		 					[doe] => 'edited'
		 				)
		 			)
		 		),
		 		'sub_table_2' => Array
		 		(
		 			[ref0] => 'mk',
		 			[id] => 'sk2',
		 			[doc] => 'created'
		 		)
		 	),
		)
	</pre>

	Definition: referenced_table
	 <pre>
		 Array
		 (
		 	[id] => 'rt_mk',
		 	[doc] => 'created',
		 	[doe] => 'updated',
		 	[ondelete] => array(
		 		[delete] => array(
		 			'sub_table_2' => 'sk2'
			 	)
			),
		)
	</pre>

	Note: 
		- For first main_table(root) to avoid complication, we recommended developer to use primary key as Numeric field, and set AUTO_INCREMENT flag.
		- When specify a value to write, you can always ommited the $_id value to tell the WRITE function to insert your $_data into database. However, this case will be usable to only root table. In case of nested data of sub table, always specify key with '$append$-xxx'; where xxx can be anything to avoid collision. Because, if developer allow PHP or JS to generate the array without key, 0, 1, 2 will always be use as the key which sometime will create undesired result.

	 */
	private $tbl_def;
	private $tbl_name;
	private $tbl_ftx;

	protected $db;
	
	function __construct($main_table_name, 
		$table_definition, 
		$table_free_text_definition = NULL)
	{
		$this->tbl_name = $main_table_name;
		$this->tbl_def = $table_definition;
		if ($table_free_text_definition != NULL)
		{
			$this->tbl_ftx = $table_free_text_definition;
		}
		else
		{
			$this->tbl_ftx = array();
		}
		$this->db = get_database_connector_instance();
	}

	/**
	 ======================= MOST front interfaces ========================= 
	 */

	public function get_tbl_name()
	{
		return $this->main_table_name;	
	}

	public function get_tbl_ftx_fields()
	{
		return $this->tbl_ftx;
	}

	// helper
	public function get_search_cond($_keyword) 
	{
		$attempt = 0;
		foreach ($this->tbl_ftx as $v) 
		{
			if ($attempt == 0)
				$param[$v . '!like'] = $_keyword;
			else
				$param[$v . '!orlike'] = $_keyword;
			$attempt ++ ;

		}
		return $param;
	}

	/**
		WARNING: Batch write (multi element write is NOT supported.)

			$_data MUST be dedicated for single writing object.
		
		@param: $re_write flag indicates if the whole object must be rewritten. (Which means all data of certain structure will be supplied. Useful when you supply all data in).
	 */
	public function write($_id, $_data, $_rewrite = FALSE) 
	{
		return $this->write_from_fao($_id, $_data, $_rewrite);
	}

	public function get($main_id = NULL, $condition = NULL, $opt = NULL)
	{
		$param['cond'] = $condition;
		$param['opt'] = $opt;
		$r = $this->read_as_fao( $main_id, $param );
		return $r;
	}
	
	public function delete_sub($refs, $sub_id_array)
	{
		$this->db->trans_start();

		$sub = $this->tbl_def;
		
		// get correct 'sub' definition.
		for( $i=0;$i<sizeof($refs);$i++)
		{
			$sub = $sub['sub'];
		}

		if (isset($sub))
		{
			foreach ($sub as $_stbl_name => $_stbl_entry)
			{
				if ( isset($sub_id_array[$_stbl_name]) )
				{
					$this->_delete_from_table(
						$refs, 
						$sub_id_array[$_stbl_name], 
						$_stbl_name, 
						$_stbl_entry);
				}
			}
		}
		
		$this->db->trans_complete();
	}

	public function delete($main_id, $sub_id_array = array())
	{
		if (is_array($main_id)==FALSE)
		{
			$ids = array($main_id);
		}
		else
		{
			$ids = $main_id;
		}
		
		$this->db->trans_start();
		
		$this->_delete_from_table(
			array(), 
			$ids,
			$this->tbl_name,
			$this->tbl_def);
		
		$this->db->trans_complete();
	}
	
	/**
	 ======================= PROTECTED interfaces ========================== 
	 */

	function _max_id($_refs, $_table_name, $_tbl_def)
	{
		$this->db->select_max($_tbl_def['id']);
		$this->db->from($_table_name);
		foreach ($_refs as $idx => $val)
		{
			$this->db->where($_tbl_def['ref'.$idx], $val);
		}
		$q = $this->db->get();
		
		foreach($q->result_array() as $v)
		{
			if (isset($v[$_tbl_def['id']]))
				return $v[$_tbl_def['id']];
		}
		// echo 'return 0';
		return -1;
	}

	function _id_exist($_refs, $_id, $_table_name, $_tbl_def)
	{
		$this->db->from($_table_name);
		foreach ($_refs as $idx => $val)
		{
			$this->db->where($_tbl_def['ref'.$idx], $val);
		}
		// process child id (specific)
		$this->db->where($_tbl_def['id'], $_id);
		return $this->db->count_all_results() > 0;
	}
	
	/**
	 * @query Primary Table Only
	 * 
	 * @return the primary id at top most of the result that met supplied condition. If failed to find, return NULL instead.
	 * 
	 */
	function whatid($prim_cond)
	{
		$this->db->select($this->tbl_def['id']);
		if (isset($prim_cond)==TRUE)
		{
			if (is_array($prim_cond)==TRUE)
			{
				foreach($prim_cond as $_k => $_v)	// $v can be either array or value.
				{
					$this->db->where($_k, $_v);
				}
			}
			else
			{
				echo 'WARNING - $prim_cond is NOT conform to read_as_fao() rules.';
			}
		}
		$this->db->from($this->tbl_name);
		$q = $this->db->get();
		
		foreach($q->result_array() as $v)
		{
			return $v[$this->tbl_def['id']];
		}
		return NULL;
	}

	function lov($field_name, $ids=null)
	{
		// Select distinct to draw list of value.
		$this->db->select($field_name)->distinct()->from($this->tbl_name);
		if (isset($ids)) 
		{
			if (is_array($ids)==true && count($ids)>0)
			{
				$this->db->where_in($ids);
			}
			else if (is_array($ids)==false)
			{
				$this->db->where($ids);
			}
		}
		$q = $this->db->get();
		
		$r = array();
		foreach($q->result_array() as $v)
		{
			$r[] = $v[$field_name];
		}
		return $r;
	}
	
	/**
	 * @param $_refs is array of reference id. 
	 * <pre>
	 * array
	 * (
	 * 		[0] => Key at top level. (main)
	 * 		[1] => Key at second level. (sub)
	 * 		...
	 * )
	 * </pre>
	 * @param $_id is single key value.
	 *  - System required to combined both $_id + $_refs to create
	 * a primary key for that table.
	 * 
	 */
	function _write_to_table($_refs = NULL, $_id = NULL, $_table_name, $_tbl_def, $_data, $_rewrite = FALSE)
	{
		// remove 'sub' items from data. And buffer them in temporary array.
		$sub_data = array();	// buffer
		$sub_tbl_defs = array();
		if (isset($_tbl_def['sub']))
		{
			foreach ($_tbl_def['sub'] as $_stbl_name => $_stbl_entry)
			{
				if (array_key_exists($_stbl_name, $_data))
				{
					$sub_data[$_stbl_name] = $_data[$_stbl_name];
					$sub_tbl_defs[$_stbl_name] = $_stbl_entry;
					unset($_data[$_stbl_name]);
				} 
				else if ($_rewrite)
				{
					$sub_data[$_stbl_name] = array();
					$sub_tbl_defs[$_stbl_name] = $_stbl_entry;
				}
			}
		}
		
		// Process $_data ..
		// write $_data on my own table context.
		if (empty($_data) == FALSE) 
		{
			if (isset($_id) && startsWith($_id, '$append$'))
			{
				// auto generate id
				// query max $_id
				$_id = $this->_max_id($_refs, $_table_name, $_tbl_def) + 1;
			}

			if ( isset($_tbl_def['doe']) && ! isset($_data[$_tbl_def['doe']]) )
			{
				$_data[$_tbl_def['doe']] = date('Y-m-d H:i:s');
			}

			if (isset($_id) && $this->_id_exist($_refs, $_id, $_table_name, $_tbl_def)==TRUE) // do UPDATE 
			{
				// process parent id
				foreach ($_refs as $idx => $val)
				{
					$this->db->where($_tbl_def['ref'.$idx], $val);
				}
				// process child id (specific)
				$this->db->where($_tbl_def['id'], $_id);
				$this->db->update($_table_name, $_data);
			}
			else		// do INSERT
			{

				if ( isset($_tbl_def['doc']) && ! isset($_data[$_tbl_def['doc']]) )
				{
					$_data[$_tbl_def['doc']] = date('Y-m-d H:i:s');
				}

				// modify $_data for write
				foreach ($_refs as $idx => $val)
				{
					$_data[$_tbl_def['ref'.$idx]] = $val;
				}
				if (isset($_id))
				{
					// if ($_id == '')
					// {
					// 	// query max $_id
					// 	$max_id = $this->_max_id($_refs, $_table_name, $_tbl_def);
					// 	$_id = $max_id + 1;
					// }
					$_data[$_tbl_def['id']] = $_id;
				}
				$this->db->insert($_table_name, $_data);
				if (isset($_id)==FALSE)
					$_id = $this->db->insert_id();
				else if (empty($_id))
					$_id = $_data[$_tbl_def['id']];
			}
		}

		// Process $sub_data
		// recursively call: write_to_table() for my sub tables.
		foreach ($sub_data as $_sub_tbl_name => $val)
		{
			if (is_array($val))
			{
				$refs = $_refs;
				$refs[] = $_id;
				$_modified_keys = array();
				$_sub_tbl_def = $sub_tbl_defs[$_sub_tbl_name];
				foreach ($val as $k => $single_val)
				{
					$this->_write_to_table(
						$refs, 
						&$k, 	// $k might be modified by $append$ case.
						$_sub_tbl_name, 
						$_sub_tbl_def, 
						$single_val,
						$_rewrite);
					$_modified_keys[] = $k;
				}
				if ($_rewrite)
				{
					// query existing id first
					// then make call to delete function.

					if (empty($_modified_keys))
						$cond = array();
					else
					{
						$attr = $_sub_tbl_def['id'] . '!not';
						$cond = array(
								$attr => $_modified_keys
							);
					}

					$to_delete = $this->_read_from_table($refs, NULL, $_sub_tbl_name, $_sub_tbl_def, $cond);
					
					log_message('debug', 'to del: ' . print_r($to_delete, TRUE) . ' AGAINST ' . print_r( $_modified_keys, TRUE) . 'tbl name = ' . $_sub_tbl_name);

					// check deletion as well.
					if (empty($to_delete) == FALSE)
					{
						$to_delete = array_keys($to_delete);	// rip key only
						// foreach ($refs as $idx => $val) 
						// {
						// 	$this->db->where($_sub_tbl_def['ref'.$idx], $val);
						// }
						// if (empty($_modified_keys) == FALSE)
						// {
						// 	$this->db->where_in($_sub_tbl_def['id'], $to_delete);
						// }
						$this->_delete_from_table(
							$refs, 
							$to_delete, 
							$_sub_tbl_name, 
							$_sub_tbl_def);
					}
				}
			}
		}
		return $_id;
	}
	
	function write_from_fao($_id, $_data, $_rewrite = FALSE)
	{
		$this->db->trans_start();
		
		$_id = $this->_write_to_table(
			array(), 
			$_id, 
			$this->tbl_name, 
			$this->tbl_def, 
			$_data,
			$_rewrite);
		
		$this->db->trans_complete();
		
		return $_id;
	}
	
	/**
	 *
	 * @return number of rows in $main_table.
	 */
	function count($prim_cond=NULL)
	{
		if (isset($prim_cond)==TRUE)
		{
			if (is_array($prim_cond))
			{
				if ( isset($prim_cond['sub_cond']) )
				{
					$sub_cond_ids = $this->_process_sub_cond(
						array(),
						NULL,
						$prim_cond,
						$this->tbl_def
					);

					if ( sizeof($sub_cond_ids) > 0) {
						$prim_cond['System_inject!where'] = $this->tbl_def['id'] . ' IN (\'' . implode('\',\'', $sub_cond_ids) . '\')';
					}
				}

				foreach($prim_cond as $_key => $_value)
				{
					$_keys = explode("!", $_key, 2);
					$_k = $_keys[0];
					$_o = (count($_keys)>1 ? $_keys[1] : NULL); // special operator assigned.
					$_v = $_value;

					if (is_null($_o)) {
						if (is_array($_v))              // if array of $_v
							$this->db->where_in($_k, $_v);
						else                            // if single value of $_v
							$this->db->where($_k, $_v);
					} else if ($_o === 'like') {
						   $this->db->like($_k, $_v);
					} else if ($_o === 'notlike') {
						   $this->db->not_like($_k, $_v);
					} else if ($_o === 'orlike') {
						   $this->db->or_like($_k, $_v);
					} else if ($_o === 'ornotlike') {
						   $this->db->or_not_like($_k, $_v);
					} else if ($_o === 'null') {
							$this->db->where($_k, NULL);
					} else if ($_o === 'notnull') {
							$this->db->where($_k . ' IS NOT NULL');
					} else if ($_o === 'ornull') {
							$this->db->or_where($_k, NULL);
					} else if ($_o === 'ornotnull') {
							$this->db->or_where($_k . ' IS NOT NULL');
					} else if ($_o === 'not') {
						if (is_array($_v))   // if array of $_v
						   $this->db->where_not_in($_k, $_v);
					   else 				// if single value of $_v
						   $this->db->where($_k . ' !=', $_v);
					} else if ($_o === 'where') {
						$this->db->where($_v, NULL, FALSE);
					} else { /* unknown operator */ }
				}
			}
			else
			{
				echo 'WARNING - $prim_cond is NOT conform to read_as_fao() rules.';
			}
		}
		$this->db->from($this->tbl_name);
		return $this->db->count_all_results();
	}
	
	/**
	Process sub condition object.
		@tier Internal function (private)

		@param prim_cond = primary conditional object.
		@return array of ids that should be included in the query.
	 */
	private function _process_sub_cond($_refs, $_id, &$_cond, $_tbl_def) {
		$sub_cond = array();	// buffer
		$_pre_query_id_result = array();

		// rip out sub condition (intersect operation must be applied.)
		foreach ($_tbl_def['sub'] as $_stbl_name => $_stbl_entry)
		{
			$_stbl_key = $_stbl_name;
			if (array_key_exists($_stbl_key, $_cond['sub_cond']))
			{
				$sub_cond[$_stbl_key] = $_cond['sub_cond'][$_stbl_key];

				$_sub_result = $this->_read_from_table(
					$_refs, 
					$_id, 
					$_stbl_name, 
					$_stbl_entry, 
					$sub_cond[$_stbl_key], 
					NULL, 
					TRUE);

				$idx = count( $_refs );
				$ref_name = $_stbl_entry['ref'.$idx];

				foreach ($_sub_result as $_sub_result_key => $_sub_result_val) {
					
					$_pre_query_id_result[] = $_sub_result_val [ $ref_name ];
				}
				unset($_cond['sub_cond'][$_stbl_name]);
			}
		}
		unset($_cond['sub_cond']);

		return $_pre_query_id_result;
	}

	function _read_from_table($_refs=NULL, $_id=NULL, $_tbl_name, $_tbl_def, $_cond=NULL, $_opt=NULL, $_deep_ref_id=FALSE)
	{
		// buffer up $_cond variable
		/// Note that $_cond will be pass in as very nested conditional array object.
		/// Therefore each level must process these variables first before using it.
		// $sub... extraction
		$sub_cond = array();	// buffer
		$filter = array();		// buffer for filtering sub tables.
		if (isset($_tbl_def['sub']) && isset($_cond))
		{
			// rip out filter
			if ( isset($_cond['filter']) ) 
			{
				foreach ($_tbl_def['sub'] as $_stbl_name => $_stbl_entry)
				{
					if (array_key_exists($_stbl_name, $_cond['filter']))
					{
						$filter[$_stbl_name] = $_cond['filter'][$_stbl_name];
						unset($_cond['filter'][$_stbl_name]);
					}
				}
				unset($_cond['filter']);
			}

			if ( isset($_cond['sub_cond']) )
			{
				$sub_cond_ids = $this->_process_sub_cond(
					$_refs,
					$_id,
					$_cond,
					$_tbl_def
				);

				if (isset($_id) == FALSE)
					$_id = array();
				$_id = array_merge($_id, $sub_cond_ids);
			}
		}
	
		// creating $q (query object).
		$this->db->select('*')->from($_tbl_name);	// select from
		$this->db->distinct();

		// process $_refs (add reference keys)
		if (isset($_refs) && is_array($_refs))
		{
			foreach ($_refs as $idx => $val)
			{
				$this->db->where($_tbl_def['ref'.$idx], $val);
			}
		}
		
		// process $_id (add where - main id)
		if (isset($_id))
		{
			if (is_array($_id) AND empty($_id)==TRUE) 
			{ 
				$this->db->where(1, 0, FALSE);
			}
			elseif (is_array($_id))	
			{
				$this->db->where_in($_tbl_def['id'], $_id);
			}
			else							// if single value of $_id
			{
				$this->db->where($_tbl_def['id'], $_id);
			}
				
		}
		
		// process $_cond (primary conditions)
		if (isset($_cond))
		{
			 if (is_array($_cond))
		 	 {
				foreach($_cond as $_key => $_value)
				{
					$_keys = explode("!", $_key, 2);
					$_k = $_keys[0];
					$_o = (count($_keys)>1 ? $_keys[1] : NULL);     // special operator assigned.
					$_v = $_value;
					
					if (is_null($_o)) {
					   if (is_array($_v))   // if array of $_v
							   $this->db->where_in($_k, $_v);
					   else 				// if single value of $_v
							   $this->db->where($_k, $_v);
					} else if ($_o === 'like') {
						   $this->db->like($_k, $_v);
					} else if ($_o === 'notlike') {
						   $this->db->not_like($_k, $_v);
					} else if ($_o === 'orlike') {
						   $this->db->or_like($_k, $_v);
					} else if ($_o === 'ornotlike') {
						   $this->db->or_not_like($_k, $_v);
					} else if ($_o === 'null') {
							$this->db->where($_k, NULL);
					} else if ($_o === 'notnull') {
							$this->db->where($_k . ' IS NOT NULL');
					} else if ($_o === 'ornull') {
							$this->db->or_where($_k, NULL);
					} else if ($_o === 'ornotnull') {
							$this->db->or_where($_k . ' IS NOT NULL');
					} else if ($_o === 'not') {
						if (is_array($_v))   // if array of $_v
						   $this->db->where_not_in($_k, $_v);
					   else 				// if single value of $_v
						   $this->db->where($_k . ' !=', $_v);
					} else if ($_o === 'where') {
						$this->db->where($_v, NULL, FALSE);
					} else { /* unknown operator */ }
				}
		   	}
			else
			{
				$this->db->where($_tbl_def['id'], $_cond);
			}
		}
		
		// process $_opt (query option)
		if (isset($_opt)==TRUE)
		{
			if (is_array($_opt)==TRUE)
			{
				unset($limit);
				unset($offset);
				foreach ($_opt as $opt_name => $opt_val)
				{
					if ($opt_name === 'sort')
					{
						$this->db->order_by($opt_val);
					}
					else if ($opt_name === 'limit')
					{
						$limit = $opt_val;
					}
					else if ($opt_name === 'offset')
					{
						$offset = $opt_val;
					}
					else
					{
						echo 'WARNING - $_opt contain unknown $opt_name: '.$opt_name;
					}
				}
				// process limit and offset.
				if (isset($limit)==TRUE)
				{
					if (isset($offset)==TRUE)
					{
						$this->db->limit($limit, $offset);
					}
					else
					{
						$this->db->limit($limit);
					}
				}
			}
			else
			{
				echo 'WARNING - $opt is NOT conform to read_as_fao() rules.';
			}
		}
		
		// retrieve result of query.
		$q = $this->db->get();
		$r = array();
		foreach ($q->result_array() as $e)	// can be multiple loop
		{
			// process result object.
			$key = $e[$_tbl_def['id']];		// buffer
			if ( ! $_deep_ref_id )
			{
				unset($e[$_tbl_def['id']]);		// remove
			}
			
			$refs = $_refs;
			$refs[] = $key;
			
			// we must read all sub entries and combined them.
			if (isset($_tbl_def['sub']))
			{
				foreach ($_tbl_def['sub'] as $_stbl_name => $_stbl_entry)
				{	
					if (isset($filter[$_stbl_name]))
						$_sub_cond = $filter[$_stbl_name];
					else
						$_sub_cond = NULL;
					
					$e[$_stbl_name] = $this->_read_from_table(
						$refs, 
						NULL, 
						$_stbl_name, 
						$_stbl_entry, 
						$_sub_cond);
				}
			}
			if ( $_deep_ref_id ) {
				$i = 0;
				while (isset($_tbl_def['ref'.$i])) {
					$deep_refs[] = $e[$_tbl_def['ref'.$i]];
					$i ++;
				}

				$deep_key = implode('/', $deep_refs);
				$r[$deep_key] = $e;
			} else {
				$r[$key] = $e;
			}
		}
		return $r;
	}
	
	/**
	 @param $param is the collection of query options. Consist of 3 main components.
	 <pre>
	 Array
	 (
	 		'primary'=>$prim_cond, nested array
	 		'sub_cond'=>$sec_cond, nested array ('Or' between each sub table.)
	 		'filter' => $filter, nested array
	 		'opt'=>$opt, nested array
	 )
	 </pre>
	 
	  '$prim_cond' must be conditional array value which conform with following format.
	  <pre>
	  Array
	  (
	  		[db_field]=>[compare_value],
	  		[db_field]=>[compare_value],
	  		...
	  )
	  </pre>
	  
	  $sec_cond and $filter is exactly the same definition. (must be conditional array value which conform with following format.) Note that this array allow you to omitted second tier array and put the direct value instead. Doing this, function will assumed the corespond key is the field that used in map out the where contraint.

	  Also, filter will only cast the where selection upon queried result. (Post process - ie. main id will be be selected if sub_condition is not satisfied.) while Sub_cond is process upon the query itself (Pre process - i.e. Select 'language', but the main id will be selected anyway.)
	  <pre>
	  Array
	  (
	  		[sub_table_id_field]=>Array
	 		(
	  			[db_field]=>[compare_value],
	  			[db_field]=>[compare_value],
	  			...
	  		),
	  		[sub_table_id_field]=>Array
	  		(
	  			[db_field]=>[compare_value],
	  			[db_field]=>[compare_value],
	  			...
	  		),
	  		[sub_table_id_field]=>{secondary_id_value}
	  )
	  </pre>
	 
	  $opt is an array of addition query request which help function caller can access to ..
	 <li>limit and offset</li>
	 <li>sort</li>
	 Array format is as follow.
	 <pre>
	 Array
	 (
	 		'sort'=>simple order by string such as 'title DESC, id ASC'
	 		'limit'=>int
	 	 	'offset'=>int
	 )
	 </pre>
	*/
	function read_as_fao($key_id=NULL, $param=NULL)
	{
		// setting that should be pass in for generic function.
		$opt = $this->_rip_entry_if_exist($param, 'opt');
		$cond = $this->_rip_entry_if_exist($param, 'cond');
		
		return $this->_read_from_table(array(), $key_id, $this->tbl_name, $this->tbl_def, $cond, $opt);
	}

	function _rip_entry_if_exist($array_object, $key)
	{
		if (isset($array_object[$key]))
		{
			return $array_object[$key];
		}
		else
		{
			return NULL;
		}
	}
	
	function _delete_from_table($refs = array(), $_ids = NULL, $_table_name, $_tbl_def)
	{
		foreach ($refs as $idx => $val) 
		{
			log_message('debug', '-> ref' . $idx . '(' . $_tbl_def['ref'.$idx] . ') / ' . $val);
			if (is_array($val))
			{
				$this->db->where_in($_tbl_def['ref'.$idx], $val);
			}
			else
			{
				$this->db->where($_tbl_def['ref'.$idx], $val);
			}
		}
		if ($_ids != NULL)
		{
			log_message('debug', '-> id (' . $_tbl_def['id'] . ') / ' . $_ids);

			if (is_array($_ids))
				$this->db->where_in($_tbl_def['id'], $_ids);
			else
				$this->db->where($_tbl_def['id'], $_ids);
		}
		$this->db->delete($_table_name);

		// @TRAP: handle 'ondelete'
		if (isset($_tbl_def['ondelete'])) {
			$this->_handle_ondelete($_ids, $_tbl_def['ondelete']);
		}

		if (isset($_tbl_def['sub']))
		{
			foreach ($_tbl_def['sub'] as $_stbl_name => $_stbl_entry)
			{
				$_refs = $refs;
				if ($_ids != NULL)
					$_refs[] = $_ids;

				$this->_delete_from_table(
					$_refs, 
					NULL, 
					$_stbl_name, 
					$_stbl_entry);
			}
		}
	}

	/**
	_handle_ondelete

	  @tier - Internal function.
	  @descitipn - call upon successful delete of entries, process each 'ondelete' definition.

	  @return void

	 */
	private function _handle_ondelete($_delete_ids, $_ondelete_def) {

		/* process action list */
		if (isset($_ondelete_def['delete'])) 
		{
			foreach($_ondelete_def['delete'] as $tbl_name => $field_name) 
			{
				if (is_array($_delete_ids))
					$this->db->where_in($field_name, $_delete_ids);
				else
					$this->db->where($field_name, $_delete_ids);
				
				$this->db->delete($tbl_name);
			}
		}
		return;
	}
}

?>